{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据库使用\n",
    "\n",
    "爬虫爬取下来的东西，我们需要保存起来，最简单的方式就是保存到Excel单子表格中，或者保存到数据库中。那么下面我们将学习如把数据保存起来。\n",
    "\n",
    "### 前期准备\n",
    "\n",
    "爬取数据，利用之前代码，获取一个歌单的信息，包括歌曲名称和歌词网站ID。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json \n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 没有网络可以用这个\n",
    "file_path = \"localhost/song_list.html\" # 读取文件的路径名称\n",
    "song_list = \"\"\n",
    "with open(file_path, 'r', encoding='gbk') as my_file:  # 刚才我们写入歌词的文本\n",
    "    song_list=my_file.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一共有50首歌\n"
     ]
    }
   ],
   "source": [
    "# line = '<a href=\"/album?id=3084625\" title=\"醒着做梦\">'\n",
    "pattern = re.compile(r'album\\?id=(.*?)\" title=\"(.*?)\"')\n",
    "song_infos = pattern.findall(song_list)\n",
    "print(f'一共有{len(song_infos)}首歌')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一共有50个歌词网站\n"
     ]
    }
   ],
   "source": [
    "line = \"\"\"<a href=\"/song?id=191232\">\"\"\"\n",
    "pattern = re.compile(r'song\\?id=(.*?)\"')\n",
    "songs = pattern.findall(song_list)\n",
    "print(f'一共有{len(songs)}个歌词网站')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 循环结果里面的数据，整理数据，为后面保存做准备。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第1首歌是《在你身边》,网站是 http://music.163.com/api/song/lyric?id=187672&lv=1&kv=1&tv=-1\n",
      "第2首歌是《遥远的她·Amour》,网站是 http://music.163.com/api/song/lyric?id=191232&lv=1&kv=1&tv=-1\n",
      "第3首歌是《忘记你我做不到》,网站是 http://music.163.com/api/song/lyric?id=189315&lv=1&kv=1&tv=-1\n",
      "第4首歌是《学友;热》,网站是 http://music.163.com/api/song/lyric?id=188432&lv=1&kv=1&tv=-1\n",
      "第5首歌是《等你等到我心痛》,网站是 http://music.163.com/api/song/lyric?id=190360&lv=1&kv=1&tv=-1\n",
      "第6首歌是《饿狼传说》,网站是 http://music.163.com/api/song/lyric?id=190270&lv=1&kv=1&tv=-1\n",
      "第7首歌是《一生跟你走作品集》,网站是 http://music.163.com/api/song/lyric?id=189987&lv=1&kv=1&tv=-1\n",
      "第8首歌是《吻别》,网站是 http://music.163.com/api/song/lyric?id=190449&lv=1&kv=1&tv=-1\n",
      "第9首歌是《友情歌;1995-2000世纪情歌金选》,网站是 http://music.163.com/api/song/lyric?id=188647&lv=1&kv=1&tv=-1\n",
      "第10首歌是《友情歌;1995-2000世纪情歌金选》,网站是 http://music.163.com/api/song/lyric?id=188674&lv=1&kv=1&tv=-1\n",
      "第11首歌是《环球DSD视听之王》,网站是 http://music.163.com/api/song/lyric?id=32689000&lv=1&kv=1&tv=-1\n",
      "第12首歌是《Celebration;45th;Anniversary;环球志101》,网站是 http://music.163.com/api/song/lyric?id=36861904&lv=1&kv=1&tv=-1\n",
      "第13首歌是《友情歌;1995-2000世纪情歌金选》,网站是 http://music.163.com/api/song/lyric?id=188657&lv=1&kv=1&tv=-1\n",
      "第14首歌是《学友;热》,网站是 http://music.163.com/api/song/lyric?id=188429&lv=1&kv=1&tv=-1\n",
      "第15首歌是《爱你多一些精选》,网站是 http://music.163.com/api/song/lyric?id=190563&lv=1&kv=1&tv=-1\n",
      "第16首歌是《等你等到我心痛》,网站是 http://music.163.com/api/song/lyric?id=190380&lv=1&kv=1&tv=-1\n",
      "第17首歌是《友情对唱》,网站是 http://music.163.com/api/song/lyric?id=188555&lv=1&kv=1&tv=-1\n",
      "第18首歌是《情缘十载》,网站是 http://music.163.com/api/song/lyric?id=189396&lv=1&kv=1&tv=-1\n",
      "第19首歌是《Jacky;Cheung;15》,网站是 http://music.163.com/api/song/lyric?id=188703&lv=1&kv=1&tv=-1\n",
      "第20首歌是《过敏世界》,网站是 http://music.163.com/api/song/lyric?id=189841&lv=1&kv=1&tv=-1\n",
      "第21首歌是《三年两语》,网站是 http://music.163.com/api/song/lyric?id=189714&lv=1&kv=1&tv=-1\n",
      "第22首歌是《情歌岁月》,网站是 http://music.163.com/api/song/lyric?id=189072&lv=1&kv=1&tv=-1\n",
      "第23首歌是《黑与白;新歌+精选;1985-2004》,网站是 http://music.163.com/api/song/lyric?id=188071&lv=1&kv=1&tv=-1\n",
      "第24首歌是《友情歌;1995-2000世纪情歌金选》,网站是 http://music.163.com/api/song/lyric?id=188671&lv=1&kv=1&tv=-1\n",
      "第25首歌是《只愿一生爱一人》,网站是 http://music.163.com/api/song/lyric?id=190808&lv=1&kv=1&tv=-1\n",
      "第26首歌是《祝福》,网站是 http://music.163.com/api/song/lyric?id=190328&lv=1&kv=1&tv=-1\n",
      "第27首歌是《吻别》,网站是 http://music.163.com/api/song/lyric?id=190473&lv=1&kv=1&tv=-1\n",
      "第28首歌是《不后悔》,网站是 http://music.163.com/api/song/lyric?id=188989&lv=1&kv=1&tv=-1\n",
      "第29首歌是《活出生命Live演唱会》,网站是 http://music.163.com/api/song/lyric?id=187937&lv=1&kv=1&tv=-1\n",
      "第30首歌是《Jacky;Cheung;15》,网站是 http://music.163.com/api/song/lyric?id=188747&lv=1&kv=1&tv=-1\n",
      "第31首歌是《真爱(新曲+真正精选)》,网站是 http://music.163.com/api/song/lyric?id=189873&lv=1&kv=1&tv=-1\n",
      "第32首歌是《宝鉴》,网站是 http://music.163.com/api/song/lyric?id=5251908&lv=1&kv=1&tv=-1\n",
      "第33首歌是《只愿一生爱一人》,网站是 http://music.163.com/api/song/lyric?id=190803&lv=1&kv=1&tv=-1\n",
      "第34首歌是《活出生命Live演唱会》,网站是 http://music.163.com/api/song/lyric?id=187908&lv=1&kv=1&tv=-1\n",
      "第35首歌是《忘记你我做不到》,网站是 http://music.163.com/api/song/lyric?id=189313&lv=1&kv=1&tv=-1\n",
      "第36首歌是《十年选》,网站是 http://music.163.com/api/song/lyric?id=94743&lv=1&kv=1&tv=-1\n",
      "第37首歌是《走过1999》,网站是 http://music.163.com/api/song/lyric?id=188820&lv=1&kv=1&tv=-1\n",
      "第38首歌是《承诺》,网站是 http://music.163.com/api/song/lyric?id=5251209&lv=1&kv=1&tv=-1\n",
      "第39首歌是《活出生命Live演唱会》,网站是 http://music.163.com/api/song/lyric?id=187944&lv=1&kv=1&tv=-1\n",
      "第40首歌是《释放自己》,网站是 http://music.163.com/api/song/lyric?id=189012&lv=1&kv=1&tv=-1\n",
      "第41首歌是《他在那里》,网站是 http://music.163.com/api/song/lyric?id=188261&lv=1&kv=1&tv=-1\n",
      "第42首歌是《醒着做梦》,网站是 http://music.163.com/api/song/lyric?id=29822035&lv=1&kv=1&tv=-1\n",
      "第43首歌是《醒着做梦》,网站是 http://music.163.com/api/song/lyric?id=29822044&lv=1&kv=1&tv=-1\n",
      "第44首歌是《林夕字传》,网站是 http://music.163.com/api/song/lyric?id=110805&lv=1&kv=1&tv=-1\n",
      "第45首歌是《等你等到我心痛》,网站是 http://music.163.com/api/song/lyric?id=190372&lv=1&kv=1&tv=-1\n",
      "第46首歌是《醒着做梦》,网站是 http://music.163.com/api/song/lyric?id=29822040&lv=1&kv=1&tv=-1\n",
      "第47首歌是《有个人》,网站是 http://music.163.com/api/song/lyric?id=188948&lv=1&kv=1&tv=-1\n",
      "第48首歌是《吻别》,网站是 http://music.163.com/api/song/lyric?id=190452&lv=1&kv=1&tv=-1\n",
      "第49首歌是《地球大合唱》,网站是 http://music.163.com/api/song/lyric?id=5285096&lv=1&kv=1&tv=-1\n",
      "第50首歌是《饿狼传说》,网站是 http://music.163.com/api/song/lyric?id=190276&lv=1&kv=1&tv=-1\n"
     ]
    }
   ],
   "source": [
    "# headers = {\"user-agent\": \"Mozilla/5.0 (Linux; Android 8.1.0; ALP-AL00 Build/HUAWEIALP-AL00; wv)\"}\n",
    "names = []\n",
    "ids = []\n",
    "webs = []\n",
    "for index, lrc_id in enumerate(songs):\n",
    "    lrc_url = 'http://music.163.com/api/song/lyric?' + 'id=' + lrc_id + '&lv=1&kv=1&tv=-1'\n",
    "    name = song_infos[index][1].replace('&nbsp',\"\") # 删掉一些多余的字符\n",
    "    print(f\"第{index+1}首歌是《{name}》,网站是 {lrc_url}\")\n",
    "    names.append(name)\n",
    "    ids.append(lrc_id)\n",
    "    webs.append(lrc_url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存到Excel\n",
    "\n",
    "我们利用Pandas来把信息保存到Excel中\n",
    "\n",
    "Pandas 使我们可以以一种所有人都能理解的方式来处理数据。它可以让我们毫不费力地从诸如 csv 类型的文件中导入数据。我们可以用它快速地对数据进行复杂的转换和过滤等操作。Pandas 真是超级棒，如果时间允许可以更多介绍这个库。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 把数据转到pandas格式--DataFrame\n",
    "\n",
    "1. names:存放了歌名\n",
    "2. ids：歌词ID\n",
    "3. webs：对应歌词网站"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "现在类型 <class 'pandas.core.frame.DataFrame'>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>歌名</th>\n",
       "      <th>歌词ID</th>\n",
       "      <th>歌词网站</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>在你身边</td>\n",
       "      <td>187672</td>\n",
       "      <td>http://music.163.com/api/song/lyric?id=187672&amp;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>遥远的她·Amour</td>\n",
       "      <td>191232</td>\n",
       "      <td>http://music.163.com/api/song/lyric?id=191232&amp;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>忘记你我做不到</td>\n",
       "      <td>189315</td>\n",
       "      <td>http://music.163.com/api/song/lyric?id=189315&amp;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学友;热</td>\n",
       "      <td>188432</td>\n",
       "      <td>http://music.163.com/api/song/lyric?id=188432&amp;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>等你等到我心痛</td>\n",
       "      <td>190360</td>\n",
       "      <td>http://music.163.com/api/song/lyric?id=190360&amp;...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           歌名    歌词ID                                               歌词网站\n",
       "0        在你身边  187672  http://music.163.com/api/song/lyric?id=187672&...\n",
       "1  遥远的她·Amour  191232  http://music.163.com/api/song/lyric?id=191232&...\n",
       "2     忘记你我做不到  189315  http://music.163.com/api/song/lyric?id=189315&...\n",
       "3        学友;热  188432  http://music.163.com/api/song/lyric?id=188432&...\n",
       "4     等你等到我心痛  190360  http://music.163.com/api/song/lyric?id=190360&..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {'歌名':names,'歌词ID':ids,'歌词网站':webs,} # 构造成字典\n",
    "df = pd.DataFrame(data_dict)  # 变成DataFrame类型\n",
    "print(\"现在类型\", type(df))\n",
    "df.head() # 观察一部分数据，检测是否正确处理数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 转到Excel里面\n",
    "df.to_excel('lrc.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQLite数据库\n",
    "\n",
    "SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。\n",
    "\n",
    "### 使用SQLite\n",
    "\n",
    "Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。\n",
    "\n",
    "在使用SQLite前，我们先要搞清楚几个概念：\n",
    "\n",
    "表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，比如学生的表，班级的表，学校的表，等等。表和表之间通过外键关联。\n",
    "\n",
    "要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；\n",
    "\n",
    "连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。\n",
    "\n",
    "Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。\n",
    "\n",
    "由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库。\n",
    "\n",
    "我们在Python交互式命令行实践一下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入SQLite驱动:\n",
    "import sqlite3\n",
    "import os# 创建一个Cursor:\n",
    "# 连接到SQLite数据库\n",
    "# 数据库文件是test.db\n",
    "# 如果文件不存在，会自动在当前目录创建:\n",
    "\n",
    "# 删掉已经存在的数据库\n",
    "db_file = 'test.db'\n",
    "if os.path.isfile(db_file):\n",
    "    os.remove(db_file)\n",
    "    cursor = conn.cursor()\n",
    "conn = sqlite3.connect('test.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个连接，连接到数据库:\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 第一步：创建歌曲(song)表\n",
    "\n",
    "要保存数据，先建立一个数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x54c1dc0>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 执行一条SQL语句，创建歌曲(song)表:\n",
    "cursor.execute('create table song (id varchar(20) primary key, name varchar(30),lrc varchar(20),url varchar(256))')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 第二步：保存数据\n",
    "\n",
    "通过循环执行插入语句，往歌曲表添加数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "insert into song (id, name, lrc, url) values ('0', '在你身边', '187672', 'http://music.163.com/api/song/lyric?id=187672&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('1', '遥远的她·Amour', '191232', 'http://music.163.com/api/song/lyric?id=191232&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('2', '忘记你我做不到', '189315', 'http://music.163.com/api/song/lyric?id=189315&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('3', '学友;热', '188432', 'http://music.163.com/api/song/lyric?id=188432&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('4', '等你等到我心痛', '190360', 'http://music.163.com/api/song/lyric?id=190360&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('5', '饿狼传说', '190270', 'http://music.163.com/api/song/lyric?id=190270&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('6', '一生跟你走作品集', '189987', 'http://music.163.com/api/song/lyric?id=189987&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('7', '吻别', '190449', 'http://music.163.com/api/song/lyric?id=190449&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('8', '友情歌;1995-2000世纪情歌金选', '188647', 'http://music.163.com/api/song/lyric?id=188647&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('9', '友情歌;1995-2000世纪情歌金选', '188674', 'http://music.163.com/api/song/lyric?id=188674&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('10', '环球DSD视听之王', '32689000', 'http://music.163.com/api/song/lyric?id=32689000&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('11', 'Celebration;45th;Anniversary;环球志101', '36861904', 'http://music.163.com/api/song/lyric?id=36861904&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('12', '友情歌;1995-2000世纪情歌金选', '188657', 'http://music.163.com/api/song/lyric?id=188657&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('13', '学友;热', '188429', 'http://music.163.com/api/song/lyric?id=188429&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('14', '爱你多一些精选', '190563', 'http://music.163.com/api/song/lyric?id=190563&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('15', '等你等到我心痛', '190380', 'http://music.163.com/api/song/lyric?id=190380&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('16', '友情对唱', '188555', 'http://music.163.com/api/song/lyric?id=188555&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('17', '情缘十载', '189396', 'http://music.163.com/api/song/lyric?id=189396&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('18', 'Jacky;Cheung;15', '188703', 'http://music.163.com/api/song/lyric?id=188703&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('19', '过敏世界', '189841', 'http://music.163.com/api/song/lyric?id=189841&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('20', '三年两语', '189714', 'http://music.163.com/api/song/lyric?id=189714&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('21', '情歌岁月', '189072', 'http://music.163.com/api/song/lyric?id=189072&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('22', '黑与白;新歌+精选;1985-2004', '188071', 'http://music.163.com/api/song/lyric?id=188071&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('23', '友情歌;1995-2000世纪情歌金选', '188671', 'http://music.163.com/api/song/lyric?id=188671&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('24', '只愿一生爱一人', '190808', 'http://music.163.com/api/song/lyric?id=190808&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('25', '祝福', '190328', 'http://music.163.com/api/song/lyric?id=190328&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('26', '吻别', '190473', 'http://music.163.com/api/song/lyric?id=190473&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('27', '不后悔', '188989', 'http://music.163.com/api/song/lyric?id=188989&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('28', '活出生命Live演唱会', '187937', 'http://music.163.com/api/song/lyric?id=187937&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('29', 'Jacky;Cheung;15', '188747', 'http://music.163.com/api/song/lyric?id=188747&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('30', '真爱(新曲+真正精选)', '189873', 'http://music.163.com/api/song/lyric?id=189873&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('31', '宝鉴', '5251908', 'http://music.163.com/api/song/lyric?id=5251908&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('32', '只愿一生爱一人', '190803', 'http://music.163.com/api/song/lyric?id=190803&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('33', '活出生命Live演唱会', '187908', 'http://music.163.com/api/song/lyric?id=187908&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('34', '忘记你我做不到', '189313', 'http://music.163.com/api/song/lyric?id=189313&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('35', '十年选', '94743', 'http://music.163.com/api/song/lyric?id=94743&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('36', '走过1999', '188820', 'http://music.163.com/api/song/lyric?id=188820&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('37', '承诺', '5251209', 'http://music.163.com/api/song/lyric?id=5251209&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('38', '活出生命Live演唱会', '187944', 'http://music.163.com/api/song/lyric?id=187944&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('39', '释放自己', '189012', 'http://music.163.com/api/song/lyric?id=189012&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('40', '他在那里', '188261', 'http://music.163.com/api/song/lyric?id=188261&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('41', '醒着做梦', '29822035', 'http://music.163.com/api/song/lyric?id=29822035&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('42', '醒着做梦', '29822044', 'http://music.163.com/api/song/lyric?id=29822044&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('43', '林夕字传', '110805', 'http://music.163.com/api/song/lyric?id=110805&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('44', '等你等到我心痛', '190372', 'http://music.163.com/api/song/lyric?id=190372&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('45', '醒着做梦', '29822040', 'http://music.163.com/api/song/lyric?id=29822040&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('46', '有个人', '188948', 'http://music.163.com/api/song/lyric?id=188948&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('47', '吻别', '190452', 'http://music.163.com/api/song/lyric?id=190452&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('48', '地球大合唱', '5285096', 'http://music.163.com/api/song/lyric?id=5285096&lv=1&kv=1&tv=-1')\n",
      "insert into song (id, name, lrc, url) values ('49', '饿狼传说', '190276', 'http://music.163.com/api/song/lyric?id=190276&lv=1&kv=1&tv=-1')\n"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "try:\n",
    "    for i in range(len(names)):\n",
    "        # 继续执行一条SQL语句，插入一条记录:\n",
    "        sql = f\"insert into song (id, name, lrc, url) values ('{i}', '{names[i]}', '{ids[i]}', '{webs[i]}')\"\n",
    "        print(sql)\n",
    "        cursor.execute(sql)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "finally:\n",
    "    # 关闭Cursor:\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提交事务:\n",
    "conn.commit()\n",
    "# 关闭Connection:\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 第三步：验证数据是否保存成功"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('0', '在你身边', '187672', 'http://music.163.com/api/song/lyric?id=187672&lv=1&kv=1&tv=-1')\n",
      "('1', '遥远的她·Amour', '191232', 'http://music.163.com/api/song/lyric?id=191232&lv=1&kv=1&tv=-1')\n",
      "('2', '忘记你我做不到', '189315', 'http://music.163.com/api/song/lyric?id=189315&lv=1&kv=1&tv=-1')\n",
      "('3', '学友;热', '188432', 'http://music.163.com/api/song/lyric?id=188432&lv=1&kv=1&tv=-1')\n",
      "('4', '等你等到我心痛', '190360', 'http://music.163.com/api/song/lyric?id=190360&lv=1&kv=1&tv=-1')\n",
      "('5', '饿狼传说', '190270', 'http://music.163.com/api/song/lyric?id=190270&lv=1&kv=1&tv=-1')\n",
      "('6', '一生跟你走作品集', '189987', 'http://music.163.com/api/song/lyric?id=189987&lv=1&kv=1&tv=-1')\n",
      "('7', '吻别', '190449', 'http://music.163.com/api/song/lyric?id=190449&lv=1&kv=1&tv=-1')\n",
      "('8', '友情歌;1995-2000世纪情歌金选', '188647', 'http://music.163.com/api/song/lyric?id=188647&lv=1&kv=1&tv=-1')\n",
      "('9', '友情歌;1995-2000世纪情歌金选', '188674', 'http://music.163.com/api/song/lyric?id=188674&lv=1&kv=1&tv=-1')\n",
      "('10', '环球DSD视听之王', '32689000', 'http://music.163.com/api/song/lyric?id=32689000&lv=1&kv=1&tv=-1')\n",
      "('11', 'Celebration;45th;Anniversary;环球志101', '36861904', 'http://music.163.com/api/song/lyric?id=36861904&lv=1&kv=1&tv=-1')\n",
      "('12', '友情歌;1995-2000世纪情歌金选', '188657', 'http://music.163.com/api/song/lyric?id=188657&lv=1&kv=1&tv=-1')\n",
      "('13', '学友;热', '188429', 'http://music.163.com/api/song/lyric?id=188429&lv=1&kv=1&tv=-1')\n",
      "('14', '爱你多一些精选', '190563', 'http://music.163.com/api/song/lyric?id=190563&lv=1&kv=1&tv=-1')\n",
      "('15', '等你等到我心痛', '190380', 'http://music.163.com/api/song/lyric?id=190380&lv=1&kv=1&tv=-1')\n",
      "('16', '友情对唱', '188555', 'http://music.163.com/api/song/lyric?id=188555&lv=1&kv=1&tv=-1')\n",
      "('17', '情缘十载', '189396', 'http://music.163.com/api/song/lyric?id=189396&lv=1&kv=1&tv=-1')\n",
      "('18', 'Jacky;Cheung;15', '188703', 'http://music.163.com/api/song/lyric?id=188703&lv=1&kv=1&tv=-1')\n",
      "('19', '过敏世界', '189841', 'http://music.163.com/api/song/lyric?id=189841&lv=1&kv=1&tv=-1')\n",
      "('20', '三年两语', '189714', 'http://music.163.com/api/song/lyric?id=189714&lv=1&kv=1&tv=-1')\n",
      "('21', '情歌岁月', '189072', 'http://music.163.com/api/song/lyric?id=189072&lv=1&kv=1&tv=-1')\n",
      "('22', '黑与白;新歌+精选;1985-2004', '188071', 'http://music.163.com/api/song/lyric?id=188071&lv=1&kv=1&tv=-1')\n",
      "('23', '友情歌;1995-2000世纪情歌金选', '188671', 'http://music.163.com/api/song/lyric?id=188671&lv=1&kv=1&tv=-1')\n",
      "('24', '只愿一生爱一人', '190808', 'http://music.163.com/api/song/lyric?id=190808&lv=1&kv=1&tv=-1')\n",
      "('25', '祝福', '190328', 'http://music.163.com/api/song/lyric?id=190328&lv=1&kv=1&tv=-1')\n",
      "('26', '吻别', '190473', 'http://music.163.com/api/song/lyric?id=190473&lv=1&kv=1&tv=-1')\n",
      "('27', '不后悔', '188989', 'http://music.163.com/api/song/lyric?id=188989&lv=1&kv=1&tv=-1')\n",
      "('28', '活出生命Live演唱会', '187937', 'http://music.163.com/api/song/lyric?id=187937&lv=1&kv=1&tv=-1')\n",
      "('29', 'Jacky;Cheung;15', '188747', 'http://music.163.com/api/song/lyric?id=188747&lv=1&kv=1&tv=-1')\n",
      "('30', '真爱(新曲+真正精选)', '189873', 'http://music.163.com/api/song/lyric?id=189873&lv=1&kv=1&tv=-1')\n",
      "('31', '宝鉴', '5251908', 'http://music.163.com/api/song/lyric?id=5251908&lv=1&kv=1&tv=-1')\n",
      "('32', '只愿一生爱一人', '190803', 'http://music.163.com/api/song/lyric?id=190803&lv=1&kv=1&tv=-1')\n",
      "('33', '活出生命Live演唱会', '187908', 'http://music.163.com/api/song/lyric?id=187908&lv=1&kv=1&tv=-1')\n",
      "('34', '忘记你我做不到', '189313', 'http://music.163.com/api/song/lyric?id=189313&lv=1&kv=1&tv=-1')\n",
      "('35', '十年选', '94743', 'http://music.163.com/api/song/lyric?id=94743&lv=1&kv=1&tv=-1')\n",
      "('36', '走过1999', '188820', 'http://music.163.com/api/song/lyric?id=188820&lv=1&kv=1&tv=-1')\n",
      "('37', '承诺', '5251209', 'http://music.163.com/api/song/lyric?id=5251209&lv=1&kv=1&tv=-1')\n",
      "('38', '活出生命Live演唱会', '187944', 'http://music.163.com/api/song/lyric?id=187944&lv=1&kv=1&tv=-1')\n",
      "('39', '释放自己', '189012', 'http://music.163.com/api/song/lyric?id=189012&lv=1&kv=1&tv=-1')\n",
      "('40', '他在那里', '188261', 'http://music.163.com/api/song/lyric?id=188261&lv=1&kv=1&tv=-1')\n",
      "('41', '醒着做梦', '29822035', 'http://music.163.com/api/song/lyric?id=29822035&lv=1&kv=1&tv=-1')\n",
      "('42', '醒着做梦', '29822044', 'http://music.163.com/api/song/lyric?id=29822044&lv=1&kv=1&tv=-1')\n",
      "('43', '林夕字传', '110805', 'http://music.163.com/api/song/lyric?id=110805&lv=1&kv=1&tv=-1')\n",
      "('44', '等你等到我心痛', '190372', 'http://music.163.com/api/song/lyric?id=190372&lv=1&kv=1&tv=-1')\n",
      "('45', '醒着做梦', '29822040', 'http://music.163.com/api/song/lyric?id=29822040&lv=1&kv=1&tv=-1')\n",
      "('46', '有个人', '188948', 'http://music.163.com/api/song/lyric?id=188948&lv=1&kv=1&tv=-1')\n",
      "('47', '吻别', '190452', 'http://music.163.com/api/song/lyric?id=190452&lv=1&kv=1&tv=-1')\n",
      "('48', '地球大合唱', '5285096', 'http://music.163.com/api/song/lyric?id=5285096&lv=1&kv=1&tv=-1')\n",
      "('49', '饿狼传说', '190276', 'http://music.163.com/api/song/lyric?id=190276&lv=1&kv=1&tv=-1')\n"
     ]
    }
   ],
   "source": [
    "# 查询记录\n",
    "conn = sqlite3.connect('test.db')\n",
    "cursor = conn.cursor()\n",
    "# 执行查询语句:\n",
    "cursor.execute('select * from song' )\n",
    "# 获得查询结果集:\n",
    "for row in cursor.fetchall():\n",
    "    print(row)\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQLAlchemy\n",
    "\n",
    "SQLAlchemy是python的一个数据库ORM工具，提供了强大的对象模型间的转换，可以满足绝大多数数据库操作的需求，并且支持多种数据库引擎（sqlite，mysql，postgres, mongodb等)\n",
    "\n",
    "首先是连接到数据库，SQLALchemy支持多个数据库引擎，不同的数据库引擎连接字符串不一样，常用的有\n",
    "\n",
    "```\n",
    "数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名\n",
    "```\n",
    "\n",
    "```\n",
    "mssql+pymssql://username:password@hostname/database\n",
    "mssql://username:password@hostname/database\n",
    "postgresql://username:password@hostname/database\n",
    "sqlite:////absolute/path/to/database\n",
    "sqlite:///c:/absolute/path/to/database\n",
    "```\n",
    "更多连接字符串的介绍参见[https://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=create_engine#database-urls]\n",
    "\n",
    "下面是连接和使用sqlite数据库的例子"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### connection\n",
    "使用传统的connection的方式连接和操作数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-11-24 15:22:32,460 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-11-24 15:22:32,464 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-11-24 15:22:32,467 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-11-24 15:22:32,470 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-11-24 15:22:32,473 INFO sqlalchemy.engine.base.Engine select * from song\n",
      "2020-11-24 15:22:32,476 INFO sqlalchemy.engine.base.Engine ()\n",
      "Data: ('0', '在你身边', '187672', 'http://music.163.com/api/song/lyric?id=187672&lv=1&kv=1&tv=-1')\n"
     ]
    }
   ],
   "source": [
    "# 数据库连接字符串\n",
    "DB_CONNECT_STRING = 'sqlite:///test.db'\n",
    "\n",
    "# 创建数据库引擎,echo为True,会打印所有的sql语句\n",
    "engine = create_engine(DB_CONNECT_STRING, echo=True)\n",
    "\n",
    "# 创建一个connection，这里的使用方式与python自带的sqlite的使用方式类似\n",
    "with engine.connect() as con:\n",
    "    # 执行sql语句，如果是增删改，则直接生效，不需要commit\n",
    "    rs = con.execute('select * from song')\n",
    "    data = rs.fetchone()\n",
    "    print(\"Data: %s\" % data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### connection事务\n",
    "使用事务可以进行批量提交和回滚"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已经有这个数据库，不用创建，继续...\n",
      "[('0', '在你身边', '187672', 'http://music.163.com/api/song/lyric?id=187672&lv=1&kv=1&tv=-1'), ('1', '遥远的她·Amour', '191232', 'http://music.163.com/api/song/lyric?id=191232&lv=1&kv=1&tv=-1'), ('2', '忘记你我做不到', '189315', 'http://music.163.com/api/song/lyric?id=189315&lv=1&kv=1&tv=-1'), ('3', '学友;热', '188432', 'http://music.163.com/api/song/lyric?id=188432&lv=1&kv=1&tv=-1'), ('4', '等你等到我心痛', '190360', 'http://music.163.com/api/song/lyric?id=190360&lv=1&kv=1&tv=-1'), ('5', '饿狼传说', '190270', 'http://music.163.com/api/song/lyric?id=190270&lv=1&kv=1&tv=-1'), ('6', '一生跟你走作品集', '189987', 'http://music.163.com/api/song/lyric?id=189987&lv=1&kv=1&tv=-1'), ('7', '吻别', '190449', 'http://music.163.com/api/song/lyric?id=190449&lv=1&kv=1&tv=-1'), ('8', '友情歌;1995-2000世纪情歌金选', '188647', 'http://music.163.com/api/song/lyric?id=188647&lv=1&kv=1&tv=-1'), ('9', '友情歌;1995-2000世纪情歌金选', '188674', 'http://music.163.com/api/song/lyric?id=188674&lv=1&kv=1&tv=-1'), ('10', '环球DSD视听之王', '32689000', 'http://music.163.com/api/song/lyric?id=32689000&lv=1&kv=1&tv=-1'), ('11', 'Celebration;45th;Anniversary;环球志101', '36861904', 'http://music.163.com/api/song/lyric?id=36861904&lv=1&kv=1&tv=-1'), ('12', '友情歌;1995-2000世纪情歌金选', '188657', 'http://music.163.com/api/song/lyric?id=188657&lv=1&kv=1&tv=-1'), ('13', '学友;热', '188429', 'http://music.163.com/api/song/lyric?id=188429&lv=1&kv=1&tv=-1'), ('14', '爱你多一些精选', '190563', 'http://music.163.com/api/song/lyric?id=190563&lv=1&kv=1&tv=-1'), ('15', '等你等到我心痛', '190380', 'http://music.163.com/api/song/lyric?id=190380&lv=1&kv=1&tv=-1'), ('16', '友情对唱', '188555', 'http://music.163.com/api/song/lyric?id=188555&lv=1&kv=1&tv=-1'), ('17', '情缘十载', '189396', 'http://music.163.com/api/song/lyric?id=189396&lv=1&kv=1&tv=-1'), ('18', 'Jacky;Cheung;15', '188703', 'http://music.163.com/api/song/lyric?id=188703&lv=1&kv=1&tv=-1'), ('19', '过敏世界', '189841', 'http://music.163.com/api/song/lyric?id=189841&lv=1&kv=1&tv=-1'), ('20', '三年两语', '189714', 'http://music.163.com/api/song/lyric?id=189714&lv=1&kv=1&tv=-1'), ('21', '情歌岁月', '189072', 'http://music.163.com/api/song/lyric?id=189072&lv=1&kv=1&tv=-1'), ('22', '黑与白;新歌+精选;1985-2004', '188071', 'http://music.163.com/api/song/lyric?id=188071&lv=1&kv=1&tv=-1'), ('23', '友情歌;1995-2000世纪情歌金选', '188671', 'http://music.163.com/api/song/lyric?id=188671&lv=1&kv=1&tv=-1'), ('24', '只愿一生爱一人', '190808', 'http://music.163.com/api/song/lyric?id=190808&lv=1&kv=1&tv=-1'), ('25', '祝福', '190328', 'http://music.163.com/api/song/lyric?id=190328&lv=1&kv=1&tv=-1'), ('26', '吻别', '190473', 'http://music.163.com/api/song/lyric?id=190473&lv=1&kv=1&tv=-1'), ('27', '不后悔', '188989', 'http://music.163.com/api/song/lyric?id=188989&lv=1&kv=1&tv=-1'), ('28', '活出生命Live演唱会', '187937', 'http://music.163.com/api/song/lyric?id=187937&lv=1&kv=1&tv=-1'), ('29', 'Jacky;Cheung;15', '188747', 'http://music.163.com/api/song/lyric?id=188747&lv=1&kv=1&tv=-1'), ('30', '真爱(新曲+真正精选)', '189873', 'http://music.163.com/api/song/lyric?id=189873&lv=1&kv=1&tv=-1'), ('31', '宝鉴', '5251908', 'http://music.163.com/api/song/lyric?id=5251908&lv=1&kv=1&tv=-1'), ('32', '只愿一生爱一人', '190803', 'http://music.163.com/api/song/lyric?id=190803&lv=1&kv=1&tv=-1'), ('33', '活出生命Live演唱会', '187908', 'http://music.163.com/api/song/lyric?id=187908&lv=1&kv=1&tv=-1'), ('34', '忘记你我做不到', '189313', 'http://music.163.com/api/song/lyric?id=189313&lv=1&kv=1&tv=-1'), ('35', '十年选', '94743', 'http://music.163.com/api/song/lyric?id=94743&lv=1&kv=1&tv=-1'), ('36', '走过1999', '188820', 'http://music.163.com/api/song/lyric?id=188820&lv=1&kv=1&tv=-1'), ('37', '承诺', '5251209', 'http://music.163.com/api/song/lyric?id=5251209&lv=1&kv=1&tv=-1'), ('38', '活出生命Live演唱会', '187944', 'http://music.163.com/api/song/lyric?id=187944&lv=1&kv=1&tv=-1'), ('39', '释放自己', '189012', 'http://music.163.com/api/song/lyric?id=189012&lv=1&kv=1&tv=-1'), ('40', '他在那里', '188261', 'http://music.163.com/api/song/lyric?id=188261&lv=1&kv=1&tv=-1'), ('41', '醒着做梦', '29822035', 'http://music.163.com/api/song/lyric?id=29822035&lv=1&kv=1&tv=-1'), ('42', '醒着做梦', '29822044', 'http://music.163.com/api/song/lyric?id=29822044&lv=1&kv=1&tv=-1'), ('43', '林夕字传', '110805', 'http://music.163.com/api/song/lyric?id=110805&lv=1&kv=1&tv=-1'), ('44', '等你等到我心痛', '190372', 'http://music.163.com/api/song/lyric?id=190372&lv=1&kv=1&tv=-1'), ('45', '醒着做梦', '29822040', 'http://music.163.com/api/song/lyric?id=29822040&lv=1&kv=1&tv=-1'), ('46', '有个人', '188948', 'http://music.163.com/api/song/lyric?id=188948&lv=1&kv=1&tv=-1'), ('47', '吻别', '190452', 'http://music.163.com/api/song/lyric?id=190452&lv=1&kv=1&tv=-1'), ('48', '地球大合唱', '5285096', 'http://music.163.com/api/song/lyric?id=5285096&lv=1&kv=1&tv=-1'), ('49', '饿狼传说', '190276', 'http://music.163.com/api/song/lyric?id=190276&lv=1&kv=1&tv=-1'), ('53', '无名', '00000', 'http://000000.html')]\n"
     ]
    }
   ],
   "source": [
    "DB_CONNECT_STRING = 'sqlite:///test.db'\n",
    "engine = create_engine(DB_CONNECT_STRING)\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    trans = connection.begin()\n",
    "    try:\n",
    "        r0 = connection.execute('create table song (id varchar(20) primary key, name varchar(30),lrc varchar(20),url varchar(256))')\n",
    "    except:\n",
    "        print(\"已经有这个数据库，不用创建，继续...\")\n",
    "    try:\n",
    "        \n",
    "        #r1 = connection.execute(\"insert into song (id, name, lrc, url) values ('53', '无名', '00000', 'http://000000.html')\")\n",
    "        r2 = connection.execute(\"select * from song\")\n",
    "        trans.commit()\n",
    "        print(r2.fetchall())  # 获取全部数据\n",
    "    except:\n",
    "        trans.rollback()\n",
    "        raise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ORM\n",
    "\n",
    "Object-Relational Mapping，把关系数据库的表结构映射到对象上\n",
    "\n",
    "上面的Song用class实例来表示"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Song(object):\n",
    "    def __init__(self, id, name, lrc, url):\n",
    "        self.id = id\n",
    "        self.name = name\n",
    "        self.lrc = lrc\n",
    "        self.url = url"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 第一步，导入SQLAlchemy，并初始化DBSession：\n",
    "\n",
    "PS: 这里一定要按顺序运行，因为每个框的代码是相互依赖，不按顺序运行会出错。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入:\n",
    "from sqlalchemy import Column, String, create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.ext.declarative import declarative_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建对象的基类:\n",
    "Base = declarative_base()\n",
    "\n",
    "# 数据库连接字符串\n",
    "DB_CONNECT_STRING = 'sqlite:///test.db'\n",
    "\n",
    "# 定义Song对象:\n",
    "class Song(Base):\n",
    "    # 表的名字:\n",
    "    __tablename__ = 'song'\n",
    "\n",
    "    # 表的结构:\n",
    "    id = Column(String(20), primary_key=True)\n",
    "    name = Column(String(30))\n",
    "    lrc = Column(String(30))\n",
    "    url = Column(String(256))\n",
    "\n",
    "# 初始化数据库连接:\n",
    "engine = create_engine(DB_CONNECT_STRING)\n",
    "# 创建DBSession类型:\n",
    "DBSession = sessionmaker(bind=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "下面，我们看看如何向数据库表中添加一行记录。\n",
    "\n",
    "由于有了ORM，我们向数据库表中添加一行记录，可以视为添加一个Song对象："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建session对象:\n",
    "session = DBSession()\n",
    "# 创建新User对象:\n",
    "new_song = Song(id='54', name='爱', lrc='18498789', url='http://1879494656.html')  # 每运行一次，id值要变化，不然会出错\n",
    "# 添加到session:\n",
    "session.add(new_song)\n",
    "# 提交即保存到数据库:\n",
    "session.commit()\n",
    "# 关闭session:\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type: <class '__main__.Song'>\n",
      "name: 爱\n"
     ]
    }
   ],
   "source": [
    "# 从数据库表中查询数据\n",
    "# 创建Session:\n",
    "session = DBSession()\n",
    "# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:\n",
    "song = session.query(Song).filter(Song.id=='54').one()\n",
    "# 打印类型和对象的name属性:\n",
    "print('type:', type(song))\n",
    "print('name:', song.name)\n",
    "# 关闭Session:\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 练习\n",
    "\n",
    "统计歌名“吻别”有多少条记录, 把每一条记录打印出来。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "# 查询记录\n",
    "conn = sqlite3.connect('test.db')\n",
    "cursor = conn.cursor()\n",
    "# 执行查询语句:\n",
    "try:\n",
    "    cursor.execute('select * from song where name=\"吻别\"' )\n",
    "    # 获得查询结果集:\n",
    "    rows = cursor.fetchall()\n",
    "    print(f\"一共有{len(rows)}记录。\")\n",
    "    for row in rows:\n",
    "        print(row)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "finally:\n",
    "    cursor.close()\n",
    "    conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
